﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace ExamPlat.DAL
{
    public class SqlHelper
    {
        private static string strCon = ConfigurationManager.AppSettings["ExamCon"];

        public static int ExecuteNonQuery(string sql)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int ExecuteScalar(string sql)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        return (int)cmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataSet ExecuteQuery(string sql)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        return ds;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //public static int ExecuteTransaction(string sql)
        //{
        //    try
        //    {
        //        using (SqlConnection conn = new SqlConnection(strCon))
        //        {
        //            conn.Open();
        //            using (SqlCommand cmd = conn.CreateCommand())
        //            {
        //                SqlTransaction trans = conn.BeginTransaction();
        //                try
        //                {
        //                    string[] sqls = sql.Split(';');
        //                    for (int i = 0; i < sqls.Length; i++)
        //                    {
        //                        cmd.Transaction = trans;
        //                        cmd.CommandText = sqls[i];
        //                        cmd.ExecuteNonQuery();
        //                    }
        //                    trans.Commit();
        //                    return 1;
        //                }
        //                catch (Exception ex)
        //                {
        //                    trans.Rollback();
        //                    throw ex;
        //                }
        //                finally
        //                {
        //                    trans.Dispose();
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}

        public static int ExecuteTransaction(string sql)
        {
            try
            {
                int id = 0;
                using (SqlConnection conn = new SqlConnection(strCon))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        SqlTransaction trans = conn.BeginTransaction();
                        try
                        {
                            string[] sqls = sql.Split(';');
                            for (int i = 0; i < sqls.Length; i++)
                            {
                                cmd.Transaction = trans;
                                if (sqls[i].Contains("@@IDENTITY"))
                                {
                                    cmd.CommandText = sqls[i];
                                    id = int.Parse(cmd.ExecuteScalar().ToString());
                                }
                                else
                                {
                                    if (sqls[i].Contains("##myID##"))
                                    {
                                        cmd.CommandText = sqls[i].Replace("##myID##", id.ToString());
                                    }
                                    else
                                    {
                                        cmd.CommandText = sqls[i];
                                    }
                                    cmd.ExecuteNonQuery();
                                }
                            }
                            trans.Commit();
                            return 1;
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            throw ex;
                        }
                        finally
                        {
                            trans.Dispose();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}